package DatabaseTest.impl;

import DatabaseTest.service.SqlService;
import lombok.SneakyThrows;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

/**
 * @ClassName SqlServiceImpl
 * @Description TODO
 * @Author ZhaoKai
 * @Date 2022-04-2909:34
 * @Version 1.0
 **/
public class SqlServiceImpl implements SqlService {

    //驱动路径
    private static final String DB_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    //数据库地址
    private static final String DB_URL = "jdbc:sqlserver://localhost:1433;DataBaseName=Bible";
    //数据库登录用户名
    private static final String DB_USER = "sa";
    //数据库用户密码
    private static final String DB_PASSWORD = "123";
    //数据库连接
    public static Connection conn = null;
    //数据库操作
    public static PreparedStatement prepareStatement = null;
    public static Statement statement = null;


    private static final String TABLE_NAME = "BibleTemp";

    static {
        try {
            Class.forName(DB_DRIVER);
            System.out.println("加载驱动成功！");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("加载驱动失败！");
        }
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            System.out.println("连接数据库成功！");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.print("SQL Server连接失败！");
        }
    }


    public void test() {
        Map<String, Object> map = new HashMap<>();
        map.put("Text", "测试3");
        if (getConnection() != null) {
            if (insertWithMap(TABLE_NAME, map) > 0) {
                System.out.println("插入成功");

                String sql = "SELECT * from BibleTemp";
                queryWithSql(sql);
            }
        }
    }

    public void test2() {
        Map<String, Object> map = new HashMap<>();
        map.put("Text", "测试2");
        map.put("Key_id", 2);
        if (getConnection() != null) {
            if (updateWithMap(TABLE_NAME, map) > 0) {
                System.out.println("更新成功");

                String sql = "SELECT * from BibleTemp where id = 2";
                queryWithSql(sql);
            }
        }
    }

    private static Connection getConnection() {
        return conn;
    }


    @SneakyThrows
    @Override
    public int insertWithMap(String tableName, Map<String, Object> map) {
        String sql = getInsertSqlWithMap(tableName, map);
        System.out.println(sql);
        //实例化Statement对象
        prepareStatement = conn.prepareStatement(sql);
        return prepareStatement.executeUpdate();
    }


    @SneakyThrows
    @Override
    public int updateWithMap(String tableName, Map<String, Object> map) {
        String sql = getUpdateSqlWithMap(tableName, map);
        System.out.println(sql);
        //实例化Statement对象
        prepareStatement = conn.prepareStatement(sql);
        return prepareStatement.executeUpdate();
    }


    @SneakyThrows
    @Override
    public ResultSet queryWithSql(String sql) {
        //实例化Statement对象
        statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            String Id = rs.getString("id");
            String text = rs.getString("Text");
            System.out.print("Id:" + Id);
            System.out.print(" Text:" + text);
            System.out.println("------------------------------------");
        }
        return rs;
    }

    /**
     * 获取更新sql
     *
     * @param tableName 表名
     * @param map       地图  Key_列名 作为查询条件
     * @return {@code String}
     */
    private String getUpdateSqlWithMap(String tableName, Map<String, Object> map) {
        String sql;
        // 使用组装sql的功能
        // 操作标识 默认为 select
        String operations = "update a set ";
        StringBuilder condition = new StringBuilder();
        String value;
        String link = "";

        for (Map.Entry<?, ?> next : map.entrySet()) {
            if (next.getValue() instanceof String) {
                value = "'" + next.getValue() + "'";
            } else {
                value = "" + next.getValue() + "";
            }
            if (next.getKey().toString().lastIndexOf("Key_") == -1) {
                condition.append(link).append("a.").append(next.getKey());
                condition.append("=").append(value);
                link = ",";
            }
        }

        // 组装 update sql 的结尾
        condition.append(" from ").append(tableName).append(" a where ");
        String and = "";
        for (Map.Entry<?, ?> next : map.entrySet()) {
            if (next.getValue() instanceof String) {
                value = "'" + next.getValue() + "'";
            } else {
                value = "" + next.getValue() + "";
            }
            String key = next.getKey().toString();
            if (key.lastIndexOf("Key_") != -1) {
                key = key.substring(key.indexOf("Key_") + 4);
                condition.append(and).append("a.").append(key).append("=").append(value);
                and = " and ";
            }
        }
        sql = operations + condition;
        return sql;
    }


    /**
     * 获取插入sql
     *
     * @param tableName 表名
     * @param map       地图
     * @return {@code String}
     */
    private String getInsertSqlWithMap(String tableName, Map<String, Object> map) {

        StringBuilder sql = new StringBuilder("insert into " +
                tableName +
                "(" +
                String.join(",", map.keySet()) +
                ") values(");

        for (Map.Entry<?, ?> next : map.entrySet()) {
            if (next.getValue() instanceof String) {
                sql.append("'").append(next.getValue()).append("'");
            } else {
                sql.append(next.getValue());
            }
            sql.append(",");
        }

        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(")");
        return String.valueOf(sql);
    }
}
